const { pool } = require("../../db"); // 导入数据库连接

// 获取全部旅游信息
exports.getAllTravel = async (keyword, local, offset, limit) => {
  // 构造查询语句
  const query = `SELECT * FROM travel WHERE name LIKE '%${keyword}%' AND location LIKE '%${local}%' LIMIT ${limit} OFFSET ${offset} `;

  try {
    // 执行查询
    const [rows] = await pool.query(query);
    // 查询总记录数
    const [counts] = await pool.query(
      `SELECT COUNT(*) FROM travel WHERE name LIKE '%${keyword}%' AND location LIKE '%${local}%'`
    );

    // 返回结果
    return {
      code: 200,
      message: "1",
      ok: true,
      data: rows,
      total: counts[0]["COUNT(*)"],
    };
  } catch (error) {
    // 返回错误信息
    return {
      code: 500,
      message: error,
      ok: false,
    };
  }
};
// 保存旅游信息
exports.saveTravel = async (body) => {
  // 解构赋值
  const {
    name, // 旅游信息名称
    category, // 旅游信息分类
    introduction, // 旅游信息介绍
    price, // 旅游信息价格
    location, // 旅游信息位置
    latitude, // 旅游信息纬度
    longitude, // 旅游信息经度
    phone, // 旅游信息电话
    rating, // 旅游信息评分
    cover, // 旅游信息封面
    images, // 旅游信息图片
    video, // 旅游信息视频
  } = body;
  // 查询待插入旅游信息的SQL语句
  const query = `INSERT INTO travel (name, category, introduction, price, location, latitude, longitude, phone, rating,is_online)
  VALUES ('${name}', '${category}', '${introduction}', ${price}, '${location}', ${latitude}, ${longitude}, '${phone}',${rating},0);`;

  try {
    // 执行SQL语句并获取结果
    const [result] = await pool.query(query);

    // 插入cover数据
    if (cover.length > 0) {
      for (const item of cover) {
        const { type, is_cover, uid, name, path, imgUrl, is_show } = item;
        const sql = `INSERT INTO media (type, is_cover, uid,name,path,imgUrl,travel_id,is_show) VALUES (?,?,?,?,?,?,?,?)`;
        // 执行SQL语句插入cover数据
        await pool.execute(sql, [
          type,
          is_cover,
          uid,
          name,
          path,
          imgUrl,
          result.insertId,
          is_show,
        ]);
      }
    }

    // 插入images数据
    if (images.length > 0) {
      for (const item of images) {
        const { type, is_cover, uid, name, path, imgUrl, is_show } = item;
        const sql = `INSERT INTO media (type, is_cover, uid,name,path,imgUrl,travel_id, is_show) VALUES (?,?,?,?,?,?,?,?)`;
        // 执行SQL语句插入images数据
        await pool.execute(sql, [
          type,
          is_cover,
          uid,
          name,
          path,
          imgUrl,
          result.insertId,
          is_show,
        ]);
      }
    }

    // 插入video数据
    if (video.length > 0) {
      for (const item of video) {
        const { type, is_cover, uid, name, path, is_show } = item;
        const sql = `INSERT INTO media (type, is_cover, uid,name,path,travel_id,is_show) VALUES (?,?,?,?,?,?,?)`;
        // 执行SQL语句插入video数据
        await pool.execute(sql, [
          type,
          is_cover,
          uid,
          name,
          path,
          result.insertId,
          is_show,
        ]);
      }
    }

    // 返回成功结果
    return {
      code: 200,
      message: "1",
      ok: true,
    };
  } catch (error) {
    // 返回服务器错误结果
    console.error(error);
    return {
      code: 500,
      message: "Internal Server Error",
      ok: false,
    };
  }
};

// 更新旅游信息
exports.updateTravel = async (body) => {
  const {
    id,
    name,
    category,
    introduction,
    price,
    location,
    latitude,
    longitude,
    phone,
    rating,
    cover,
    images,
    video,
  } = body;

  const connection = await pool.getConnection();
  await connection.beginTransaction();

  try {
    const updateQuery = `UPDATE travel SET name=?, category=?, introduction=?, price=?, location=?, latitude=?, longitude=?, phone=?, rating=? WHERE id=?`;
    const updateParams = [
      name,
      category,
      introduction,
      price,
      location,
      latitude,
      longitude,
      phone,
      rating,
      id,
    ];
    const [updateResult] = await connection.query(updateQuery, updateParams);

    const deleteMediaQuery = `DELETE FROM media WHERE travel_id=?`;
    const deleteMediaParams = [id];
    await connection.query(deleteMediaQuery, deleteMediaParams);

    // 插入 cover 数据
    if (cover.length > 0) {
      const coverInsertQuery = `INSERT INTO media (type, is_cover, uid, name, path, imgUrl, travel_id,is_show) VALUES ?`;
      const coverInsertParams = cover.map((item) => [
        item.type,
        item.is_cover,
        item.uid,
        item.name,
        item.path,
        item.imgUrl,
        id,
        item.is_show,
      ]);
      await connection.query(coverInsertQuery, [coverInsertParams]);
    }

    // 插入 images 数据
    if (images.length > 0) {
      const imagesInsertQuery = `INSERT INTO media (type, is_cover, uid, name, path, imgUrl, travel_id,is_show) VALUES ?`;
      const imagesInsertParams = images.map((item) => [
        item.type,
        item.is_cover,
        item.uid,
        item.name,
        item.path,
        item.imgUrl,
        id,
        item.is_show,
      ]);
      await connection.query(imagesInsertQuery, [imagesInsertParams]);
    }

    // 插入 video 数据
    if (video.length > 0) {
      const videoInsertQuery = `INSERT INTO media (type, is_cover, uid, name, path, travel_id,is_show) VALUES ?`;
      const videoInsertParams = video.map((item) => [
        item.type,
        item.is_cover,
        item.uid,
        item.name,
        item.path,
        id,
        item.is_show,
      ]);
      await connection.query(videoInsertQuery, [videoInsertParams]);
    }

    // 提交事务
    await connection.commit();
    connection.release();

    if (updateResult.affectedRows) {
      return {
        code: 200,
        message: "1",
        ok: true,
      };
    } else {
      return {
        code: 500,
        message: "0",
        ok: false,
      };
    }
  } catch (error) {
    // 回滚事务
    await connection.rollback();
    connection.release();
    console.error(error);
    res.status(500).send("Internal Server Error");
  }
};

// 删除单个旅游信息
exports.deleteTravel = async (id) => {
  const query1 = `DELETE FROM comment WHERE travel_id=${id};`;
  const query = `Delete from travel where id=${id};`;
  try {
    await pool.query(query);
    return {
      code: 200,
      message: "1",
      ok: true,
    };
  } catch (error) {
    return {
      code: 500,
      message: error,
      ok: false,
    };
  }
};

// 获取对应媒体信息
exports.getMedia = async (id) => {
  const query = `SELECT * FROM media WHERE travel_id=${id}`;
  try {
    const [rows] = await pool.query(query);
    return {
      code: 200,
      message: "1",
      ok: true,
      data: rows,
    };
  } catch (err) {
    return {
      code: 500,
      message: err,
      ok: false,
    };
  }
};

exports.updateIsOnline = async (id, isOnline) => {
  const query = `UPDATE travel SET is_online = ${isOnline} WHERE id = ${id}`;
  try {
    await pool.query(query);
    return { code: 200, message: "更新成功" };
  } catch (error) {
    return { code: 500, message: "更新失败" };
  }
};
